<?php
require_once ('library/Model/BaseModel.php');
require_once 'Controller/UserController.php';
/**
 *
 * @author Boo
 *        
 */
class Route extends BaseModel implements IModel {
	/**
	 * get price by trip id
	 * 
	 * @param int $tripid        	
	 * @return price in buy
	 */
	public function getPrice($tripid) {
		$sql = 'select 	t.saleoff as saleoff,
						r.price as price
				from route as r
				left join trip as t on t.route = r.id';
		echo $sql;
		$b = $this->db->fetchRow ( $sql );
		return $b ['saleoff'] * $b ['price'];
	}
	/*
	 * (non-PHPdoc) @see BaseModel::getall()
	 */
	public function getall($strlimit = NULL) {
		// TODO Auto-generated method stub
		$sql = 'select r.id,r.code,u.username as usercreate,p.name as pointstart,p1.name as pointend,r.price,r.status
				from route as r 
				left join user as u on r.user = u.id
				left join point as p on p.route = r.id and p.position in(select min(p.position) from point as p where p.route=r.id)
				left join point as p1 on p1.route = r.id and p1.position in(select max(p1.position) from point as p1 where p1.route=r.id)
				order by r.id ASC '.$strlimit;
		echo $sql;
		$listentity = $this->db->fetchAll ( $sql );
		return $listentity;
	}
	/**
	 *
	 * @param unknown $routeid        	
	 */
	public function getFirstPointOfRoute($routeid) {
		$sql = 'select p.name,p.position
				from point as p where p.route = ' . $routeid . ' order by p.position asc limit 0,1';
		$x = $this->db->fetchRow ( $sql );
		return $x['name'];
	}
	/**
	 *
	 * @param unknown $routeid        	
	 */
	public function getEndPointOfRoute($routeid) {
		$sql = 'select p.name,p.position
				from point as p where p.route = ' . $routeid . ' order by p.position asc limit 0,1';
		$x = $this->db->fetchRow ( $sql );
		return $x['name'];
	}
	/**
	 *
	 * @param unknown $tripid        	
	 * @return Ambigous <a, NULL, unknown>
	 */
	public function getNameRouteByTrip($tripid) {
		$sql = 'select p.name as pointstart,p1.name as pointend
				from trip as t
				left join point as p on p.route = t.route and p.position in(select min(p.position) from point as p where p.route=t.route)
				left join point as p1 on p1.route = t.route and p1.position in(select max(p1.position) from point as p1 where p1.route=t.route)
				where t.id =' . $tripid;
		return $this->db->fetchRow ( $sql );
	}
	/**
	 * get all name route by trip id
	 */
	public function getallNameRouteByTrip() {
		$sql = 'select t.id as tripid, p.name as pointstart,p1.name as pointend
				from trip as t
				left join point as p on p.route = t.route and p.position in(select min(p.position) from point as p where p.route=t.route)
				left join point as p1 on p1.route = t.route and p1.position in(select max(p1.position) from point as p1 where p1.route=t.route)';
		return $this->db->fetchAll ( $sql );
	}
	/*
	 * (non-PHPdoc) @see BaseModel::insert()
	 */
	public function insert(array $entity) {
		// TODO Auto-generated method stub
		$userifo = Auth::getInstance ();
		if (isset ( $userifo ) != NULL) {
			$entity ['code'] = $this->generateCode ($userifo['id']);
			$sql = "insert into route (";
			$c = $b = 0;
			foreach ( $entity as $key => $value ) {
				
				if ($c == 0) {
					$sql .= $key;
					$c = 1;
				} else
					$sql .= "," . $key;
			}
			$sql .= ")";
			$sql .= " values (";
			foreach ( $entity as $key => $value ) {
				if ($b == 0) {
					$sql .= "'" . $value . "'";
					$b = 1;
				} else
					$sql .= "," . "'" . $value . "'";
			}
			$sql .= ")";
			return $this->db->execute ( $sql );
		}
	}
	/*
	 * (non-PHPdoc) @see BaseModel::generateCode()
	 */
	public function generateCode($key) {
		// TODO Auto-generated method stub
		$sql = 'select code from user where id = ' . $key;
		$code = $this->db->fetchRow ( $sql );
		$code = substr ( $code ['code'], 0, 3 ) . 'R';
		$xsql = 'select code from route where code like "' . $code . '%" order by code DESC limit 0,1';
		$x = $this->db->fetchRow ( $xsql );
		$x = intval ( substr ( $x ['code'], 4, strlen ( $x ['code'] ) ) ) + 1;
		if ($x < 10)
			return $code = $code . '0' . $x;
		return $code = $code . $x;
	}
	
	/**
	 *  lay Name Route
	 */
	public function getNameRoute(){
		$sql = 'select r.id as routeid, p.name as pointstart,p1.name as pointend
				from route as r
				left join point as p on p.route = r.id and p.position in(select min(p.position) from point as p where p.route=r.id)
				left join point as p1 on p1.route = r.id and p1.position in(select max(p1.position) from point as p1 where p1.route=r.id)';
		return $this->db->fetchAll ( $sql );
	}
	/**
	 * 
	 * @param unknown $iduser
	 * @return Ambigous <>
	 */
	public function getUserbyRoute($iduser){
		$sql = 'select name from user where id = '.$iduser;
		$x = $this->db->fetchRow($sql);
		return $x['username'];
	}
	/**
	 * 
	 * @param unknown $idroute
	 * list name in route
	 */
	public function getAllNameinRoute($idroute){
		$sql = 'select * from point as p where route = '.$idroute.' order by position asc';
		return $this->db->fetchAll($sql);
	}
}
?>